package com.workshop.dao;

import com.workshop.entity.AArticle;
import com.baomidou.mybatisplus.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author chengluqiang
 * @since 2019-04-13
 */
public interface AArticleDao extends BaseMapper<AArticle> {
    /**
     * 未截稿的文章
     * @author chenluqiang
     * @date 2019/4/13
     * @param [sqlStart, pageTotal]
     * @return java.lang.Object
     */
    @Select("SELECT  " +
            "  art.id AS artId,  " +
            "  art.title,  " +
            "  art.url artUrl,  " +
            "  art.seat artSeat,  " +
            "  art.startTime,  " +
            "  art.pressTime  " +
            "FROM  " +
            "  a_article art  " +
            "WHERE  " +
            "  DATEDIFF(art.pressTime, NOW())>=0 " +
            "  AND art.status = 1  " +
            " ORDER BY art.create_time DESC" +
            " LIMIT #{pageStart},#{pageTotal}")
    List<Map<String,Object>> getNotAcceptAritcles(@Param("pageStart") long sqlStart, @Param("pageTotal")long pageTotal);

    /**
     * 已截稿的文章
     * @author chenluqiang
     * @date 2019/4/13
     * @param [sqlStart, pageTotal]
     * @return java.lang.Object
     */
    @Select("SELECT  " +
            "  art.id AS artId,  " +
            "  art.title,  " +
            "  art.url artUrl,  " +
            "  art.seat artSeat,  " +
            "  art.startTime,  " +
            "  art.pressTime  " +
            "FROM  " +
            "  a_article art  " +
            "WHERE  " +
            "  DATEDIFF(art.pressTime, NOW())<0" +
            "  AND art.status = 1  " +
             " ORDER BY art.create_time DESC" +
            " LIMIT #{pageStart},#{pageTotal}")
    List<Map<String,Object>> getStopAcceptAritcles(@Param("pageStart") long sqlStart,@Param("pageTotal")long pageTotal);
    /**
     * 所有文章
     * @author chenluqiang
     * @date 2019/4/13
     * @param [sqlStart, pageTotal]
     * @return java.lang.Object
     */
    @Select("SELECT  " +
            "  art.id AS artId,  " +
            "  art.title,  " +
            "  art.url artUrl,  " +
            "  art.seat artSeat,  " +
            "  art.startTime,  " +
            "  art.endTime,  " +
            "  art.pressTime  " +
            "FROM  " +
            "  a_article art  " +
            "  WHERE art.status = 1  " +
            " ORDER BY art.create_time DESC"+
            " LIMIT #{pageStart},#{pageTotal}")
    List<Map<String,Object>> allArticles(@Param("pageStart") long sqlStart,@Param("pageTotal")long pageTotal);

    @Select("SELECT  " +
            "  art.id AS artId,  " +
            "  art.title,  " +
            "  art.url artUrl,  " +
            "  art.seat artSeat,  " +
            "  art.startTime,  " +
            "  art.endTime,  " +
            "  (CASE art.status WHEN 0 THEN '待审核' WHEN 1 THEN '审核通过'  ELSE  '审核不通过' END) status,  " +
            "  art.pressTime  " +
            "FROM  " +
            "  a_article art  " +
            " ORDER BY art.create_time DESC"+
            " LIMIT #{pageStart},#{pageTotal}")
    List<Map<String,Object>> allAdminArticles(@Param("pageStart") long sqlStart,@Param("pageTotal")long pageTotal);
    
   /* @Select("SELECT  " +
    		"  art.id AS artId,  " +
            "  art.title,  " +
            "  art.url artUrl,  " +
            "  art.seat artSeat,  " +
            "  art.startTime,  " +
            "  art.endTime,  " +
            "  art.pressTime  " +
            "FROM  " +
            "  a_article art  " +
            "LEFT JOIN a_article_key ark ON ark.articleId = art.id\n" +
            "  WHERE art.status = 1  " +
            " AND ark.categoryId IN (#{categorys})" +
            " ORDER BY art.create_time DESC"+
            " LIMIT #{pageStart},#{pageTotal}")*/
    List<Map<String,Object>> commendedArticles(@Param("pageStart") long sqlStart,@Param("pageTotal")long pageTotal,@Param("categorys")List<String> categorys);

    @Select("SELECT\n" +
    		"  acy.`id` categoryId,\n" +
            "  acy.`name` categoryName\n" +
            "FROM\n" +
            "\ta_article art\n" +
            "LEFT JOIN a_article_key ark ON ark.articleId = art.id\n" +
            "LEFT JOIN a_category acy ON acy.id = ark.categoryId\n" +
            "WHERE art.id = #{artId}")
    List<Map<String,Object>> getCategorysByArtId(@Param("artId")String artId);
    @Select("SELECT\n" +
            "  acy.`name` categoryName\n" +
            "FROM\n" +
            "\ta_article art\n" +
            "LEFT JOIN a_article_key ark ON ark.articleId = art.id\n" +
            "LEFT JOIN a_category acy ON acy.id = ark.categoryId\n" +
            "WHERE art.id = #{artId}")
    List<String> getCategorysByArtId2(@Param("artId")String artId);
    
    /**
     * 详情
     * @author chenluqiang
     * @date 2019/4/14
     * @param [artId]
     * @return java.util.Map<java.lang.String,java.lang.Object>
     */
    @Select("SELECT  " +
            "  art.id AS artId,  " +
            "  art.title,  " +
            "  art.url artUrl,  " +
            "  art.seat artSeat,  " +
            "  art.startTime,  " +
            "  art.endTime,  " +
            "  art.pressTime,  " +
            "  art.content  " +
            "FROM  " +
            "  a_article art  " +
            " WHERE art.id = #{artId}")
    Map<String,Object> getArticleById(@Param("artId") String artId);

    @Select("SELECT  " +
            "  art.id AS artId,  " +
            "  art.title,  " +
            "  art.url artUrl,  " +
            "  art.seat artSeat,  " +
            "  art.startTime,  " +
            "  art.pressTime  " +
            "FROM  " +
            "  a_article art  " +
            "LEFT JOIN a_article_key ark ON ark.articleId = art.id\n" +
            "LEFT JOIN a_category acy ON acy.id = ark.categoryId\n" +
            " WHERE (art.title LIKE CONCAT('%',#{keyword},'%') OR acy.name LIKE CONCAT('%',#{keyword},'%'))"+
            "  AND art.status = 1  " +
            " ORDER BY art.create_time DESC"+
            " LIMIT #{pageStart},#{pageTotal}")
    List<Map<String,Object>> searchList(@Param("keyword") String keyword,@Param("pageStart") long sqlStart,@Param("pageTotal")long pageTotal);
    
    @Select("SELECT  " +
    		"  art.startTime,  " +
    		"  art.id artId,  " +
            "  art.title  " +
            "FROM " + 
            "	u_user usr " + 
            "LEFT JOIN u_user_art_key uua ON usr.id = uua.userId " + 
            "LEFT JOIN a_article art ON art.id = uua.artId  " +
            "WHERE DATEDIFF(art.startTime, NOW())>=0 " +
            "  AND usr.id = #{userId}  " +
            "  AND art.status = 1  " +
            " ORDER BY art.create_time DESC" +
            " LIMIT #{pageStart},#{pageTotal}")
	List<Map<String, Object>> getNoteList(@Param("pageStart") long sqlStart,@Param("pageTotal")long pageTotal,@Param("userId")String userId);
    @Select("SELECT  " +
            "  COUNT(art.id)  " +
            "FROM  " +
            "  a_article art  ")
    long getArticlesCount();
    
    @Select("SELECT\n" + 
    		"	SUM(auc.count) sCount,\n" + 
    		"	auc.categoryId\n" + 
    		"FROM\n" + 
    		"	a_user_count auc\n" + 
    		"WHERE\n" + 
    		"	auc.userId = #{userId}\n" + 
    		"GROUP BY\n" + 
    		"	auc.categoryId\n" + 
    		"ORDER BY sCount DESC")
    List<LinkedHashMap<String, Object>> getCountAndCategoryId(@Param("userId")String userId);
}
